package com.h3bpm.web.common.controller;

import OThinker.Common.DotNetToJavaStringHelper;
import OThinker.H3.Controller.ControllerBase;
import OThinker.H3.Entity.WorkItem.WorkItemModels.WorkItemFinished;
import com.alibaba.fastjson.JSONObject;
import com.h3bpm.handler.JSONUtils;
import com.h3bpm.web.common.domin.Attachment;
import com.h3bpm.web.common.domin.FormData;
import com.h3bpm.web.common.utils.SqlUtil;
import com.h3bpm.web.instance.consultingOutcomeDocument.dao.MagnitudeService;
import com.h3bpm.web.util.AESUtil;
import com.h3bpm.web.util.DateUtil;
import data.DataRow;
import data.DataTable;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.io.IOException;
import java.util.*;

/**
 * @Author: VillWang
 * @Date: 2020/5/31 11:19
 */
@RestController
@RequestMapping("/Portal/InstanceController")
public class InstancesController extends ControllerBase {

    private static Logger logger = LoggerFactory.getLogger(InstancesController.class);
    /**
     *
     */
    @Autowired
    MagnitudeService magnitudeService;
    @Override
    public String getFunctionCode() {
        return null;
    }

    @RequestMapping("/getCategory")
    public JSONObject organizationSync(String businessCategoryId){
        JSONObject result = new JSONObject();
        try {
        String businessCategory="";
        logger.info("获取业务类别的名称————businessCategoryId——"+businessCategoryId);
        if(StringUtils.isNotBlank(businessCategoryId)){
           String sql="select menu_name as businessCategory  from bms_md_business_category  where guid = '" +businessCategoryId+"'";

            DataTable table = SqlUtil.doQuery(sql);
            List<DataRow> entityRows = table.getEntityRows();
            if(!entityRows.isEmpty()&&entityRows.size()>0) {
                businessCategory= entityRows.get(0).getString("businessCategory");
                result.put("state", "200");
                result.put("date", businessCategory);
                result.put("msg", "success");
                return result;
            }
         }
       }catch (Exception e){
        e.printStackTrace();
       }
       return result;
    }
    @PostMapping("/starInstance")
    public JSONObject starInstance(@RequestBody FormData formData){
        JSONObject result = new JSONObject();
        try {
            logger.info("存储表单数据————formData——"+formData);
            if(StringUtils.isNotBlank(formData.getBusinessCategoryId())){
                String formId= UUID.randomUUID().toString();
                String businessCategoryId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessCategoryId())?"":formData.getBusinessCategoryId();
                String businessCategory=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessCategory())?"":formData.getBusinessCategory();
                String deptName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getDeptName())?"":formData.getDeptName();
                String deptId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getDeptId())?"":formData.getDeptId();
                String projectName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectName())?"":formData.getProjectName();
                String projectId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectId())?"":formData.getProjectId();
                String projectLeader=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectLeader())?"":formData.getProjectLeader();
                String projectLeaderId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectLeaderId())?"":formData.getProjectLeaderId();
                long currentTime=System.currentTimeMillis();
                String businessId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessId())?"":formData.getBusinessId();
                String businessName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessName())?"":formData.getBusinessName();
                String projectManager=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectManager())?"":formData.getProjectManager();
                String projectManagerNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectManagerNo())?"":formData.getProjectManagerNo();
                String reportName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getReportName())?"":formData.getReportName();
                String projectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectLeaderNo())?"":formData.getProjectLeaderNo();
                String projectCode=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectCode())?"":formData.getProjectCode();
                String members=DotNetToJavaStringHelper.isNullOrEmpty(formData.getMembers())?"":formData.getMembers();
                String referReportName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getReferReportName())?"":formData.getReferReportName();
                String reportId = DotNetToJavaStringHelper.isNullOrEmpty(formData.getReportId())?"":formData.getReportId();
                String reportNo = DotNetToJavaStringHelper.isNullOrEmpty(formData.getReportNo())?"":formData.getReportNo();
                String referReportId = DotNetToJavaStringHelper.isNullOrEmpty(formData.getReferReportId())?"":formData.getReferReportId();
                String projectGlobalId = DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectGlobalId())?"":formData.getProjectGlobalId();
                String reportType = DotNetToJavaStringHelper.isNullOrEmpty(formData.getReportType())?"":formData.getReportType();
                String businessNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessNo())?"":formData.getBusinessNo();
                String agencyId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyId())?"":formData.getAgencyId();
                String agencyNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyNo())?"":formData.getAgencyNo();
                String sectionName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getSectionName())?"":formData.getSectionName();
                String agencyName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyName())?"":formData.getAgencyName();
                String costName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getCostName())?"":formData.getCostName();
                String costNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getCostNo())?"":formData.getCostNo();
                String costId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getCostId())?"":formData.getCostId();
                String applicationNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getApplicationNo())?"":formData.getApplicationNo();//沪报建编号
                String otherNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getOtherNo())?"":formData.getOtherNo();//沪标段号
                String reportTypeCounts=DotNetToJavaStringHelper.isNullOrEmpty(formData.getReportTypeCounts())?"":formData.getReportTypeCounts();//报告类型数
                String newProjectLeader=DotNetToJavaStringHelper.isNullOrEmpty(formData.getNewProjectLeader())?"":formData.getNewProjectLeader();//迁移新的项目负责人
                String newProjectName=DotNetToJavaStringHelper.isNullOrEmpty(formData.getNewProjectName())?"":formData.getNewProjectName();//迁移新的项目名称
                String newProjectCode=DotNetToJavaStringHelper.isNullOrEmpty(formData.getNewProjectCode())?"":formData.getNewProjectCode();//迁移新的项目编码
                String businessStatusRemark=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessStatusRemark())?"":formData.getBusinessStatusRemark();//业务状态备注
                String businessStartTime=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessStartTime())?null: "'"+DateUtil.format(DateUtil.parse(formData.getBusinessStartTime(), "yyyy-MM-dd"),"yyyy-MM-dd")+"'";
                String businessEndTime =DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessEndTime())?null: "'"+DateUtil.format(DateUtil.parse(formData.getBusinessEndTime(), "yyyy-MM-dd"),"yyyy-MM-dd")+"'";
                String businessStatus=DotNetToJavaStringHelper.isNullOrEmpty(formData.getBusinessStatus())?"":formData.getBusinessStatus();
                String newProjectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getNewProjectLeaderNo())?"":formData.getNewProjectLeaderNo();
                String projectCompany=DotNetToJavaStringHelper.isNullOrEmpty(formData.getProjectCompany())?"":formData.getProjectCompany();

                String agencyProjectLeaderId=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyProjectLeaderId())?"":formData.getAgencyProjectLeaderId();
                String agencyProjectLeader=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyProjectLeader())?"":formData.getAgencyProjectLeader();
                String agencyProjectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty(formData.getAgencyProjectLeaderNo())?"":formData.getAgencyProjectLeaderNo();
                String  sql = "INSERT INTO e_form VALUES('"+businessCategoryId+"','"+formId+"','"+businessCategory+
                        "','" +deptName+"','"+deptId+"','"+projectName+"','"+projectId+"',"+currentTime+",'"+projectLeader+"','"+
                        projectLeaderId+"','"+businessId+"','"+businessName+"','"+projectManager+"','"
                        +projectManagerNo+"','"+reportName+"','"+projectLeaderNo+"','"+projectCode+"','"+members+"','"
                        +referReportName+"','"+reportNo+"','"+reportId+"','"+projectGlobalId+"','"+referReportId+"','"+reportType+
                        "','"+businessNo+"','"+agencyId+"','"+agencyNo+"','"+sectionName+"','"+agencyName+
                        "','"+costName+"','"+costNo+"','"+costId+"','"+applicationNo+"','"+otherNo+"','"+reportTypeCounts+
                        "','"+newProjectLeader+"','"+newProjectName+"','"+newProjectCode+"','"+businessStatusRemark+
                        "',"+businessStartTime+","+businessEndTime+",'"+businessStatus+"','"+newProjectLeaderNo+
                        "','"+projectCompany+"','"+agencyProjectLeaderId+"','"+agencyProjectLeader+"','"+agencyProjectLeaderNo+"')";
                int res= SqlUtil.doNoQuery(sql);
                if(res>0){
                    result.put("state", "200");
                    result.put("date", AESUtil.encrypt(String.valueOf(currentTime)));
                    result.put("msg", "success");
                    result.put("formId", formId);
                    return result;
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
    @PostMapping("/starInstance2")
    public JSONObject starInstance(@RequestBody String json){
        JSONObject result = new JSONObject();
        try {
            logger.info("存储表单数据————json——"+json);
            if(StringUtils.isNotBlank(json)){
                Map<String, Object> stringObjectMap = null;
                stringObjectMap = JSONUtils.parseMap(json);
                String formId= UUID.randomUUID().toString();
                String businessCategoryId= DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessCategoryId"))?"":(String) stringObjectMap.get("businessCategoryId");
                String businessCategory=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessCategory"))?"":(String) stringObjectMap.get("businessCategory");
                String deptName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("deptName"))?"":(String) stringObjectMap.get("deptName");
                String deptId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("deptId"))?"":(String) stringObjectMap.get("deptId");
                String projectName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectName"))?"":(String) stringObjectMap.get("projectName");
                String projectId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectId"))?"":(String) stringObjectMap.get("projectId");
                String projectLeader=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectLeader"))?"":(String) stringObjectMap.get("projectLeader");
                String projectLeaderId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectLeaderId"))?"":(String) stringObjectMap.get("projectLeaderId");
                long currentTime=System.currentTimeMillis();
                String businessId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessId"))?"":(String) stringObjectMap.get("businessId");
                String businessName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessName"))?"":(String) stringObjectMap.get("businessName");
                String projectManager=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectManager"))?"":(String) stringObjectMap.get("projectManager");
                String projectManagerNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectManagerNo"))?"":(String) stringObjectMap.get("projectManagerNo");
                String reportName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("reportName"))?"":(String) stringObjectMap.get("reportName");
                String projectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectLeaderNo"))?"":(String) stringObjectMap.get("projectLeaderNo");
                String projectCode=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectCode"))?"":(String) stringObjectMap.get("projectCode");
                String members=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("members"))?"":(String) stringObjectMap.get("members");
                String referReportName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("referReportName"))?"":(String) stringObjectMap.get("referReportName");
                String reportId =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("reportId"))?"": (String) stringObjectMap.get("reportId");
                String reportNo =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("reportNo"))?"": (String) stringObjectMap.get("reportNo");
                String referReportId =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("referReportId"))?"": (String) stringObjectMap.get("referReportId");
                String projectGlobalId =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectGlobalId"))?"": (String) stringObjectMap.get("projectGlobalId");
                String reportType =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("reportType"))?"": (String) stringObjectMap.get("reportType");
                String businessNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessNo"))?"":(String) stringObjectMap.get("businessNo");
                String agencyId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyId"))?"":(String) stringObjectMap.get("agencyId");
                String agencyNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyNo"))?"":(String) stringObjectMap.get("agencyNo");
                String sectionName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("sectionName"))?"":(String) stringObjectMap.get("sectionName");
                String agencyName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyName"))?"":(String) stringObjectMap.get("agencyName");
                String costName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("costName"))?"":(String) stringObjectMap.get("costName");
                String costNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("costNo"))?"":(String) stringObjectMap.get("costNo");
                String costId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("costId"))?"":(String) stringObjectMap.get("costId");
                String applicationNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("applicationNo"))?"":(String) stringObjectMap.get("applicationNo");//沪报建编号
                String otherNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("otherNo"))?"":(String) stringObjectMap.get("otherNo");//沪标段号
                String reportTypeCounts=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("reportTypeCounts"))?"":(String) stringObjectMap.get("reportTypeCounts");//报告类型数
                String newProjectLeader=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("newProjectLeader"))?"":(String) stringObjectMap.get("newProjectLeader");//迁移新的项目负责人
                String newProjectName=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("newProjectName"))?"":(String) stringObjectMap.get("newProjectName");//迁移新的项目名称
                String newProjectCode=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("newProjectCode"))?"":(String) stringObjectMap.get("newProjectCode");//迁移新的项目编码
                String businessStatusRemark=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessStatusRemark"))?"":(String) stringObjectMap.get("businessStatusRemark");//业务状态备注
                String businessStartTime=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessStartTime"))?null:"'"+DateUtil.format(DateUtil.parse((String)stringObjectMap.get("businessStartTime"),"yyyy-MM-dd"),"yyyy-MM-dd")+"'";
                String businessEndTime =DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessEndTime"))?null:"'"+DateUtil.format(DateUtil.parse((String)stringObjectMap.get("businessEndTime"),"yyyy-MM-dd"),"yyyy-MM-dd")+"'";
                String businessStatus=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("businessStatus"))?"":(String) stringObjectMap.get("businessStatus");
                String newProjectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("newProjectLeaderNo"))?"":(String) stringObjectMap.get("newProjectLeaderNo");
                String projectCompany=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("projectCompany"))?"":(String) stringObjectMap.get("projectCompany");

                String agencyProjectLeaderId=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyProjectLeaderId"))?"":(String) stringObjectMap.get("agencyProjectLeaderId");
                String agencyProjectLeader=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyProjectLeader"))?"":(String) stringObjectMap.get("agencyProjectLeader");
                String agencyProjectLeaderNo=DotNetToJavaStringHelper.isNullOrEmpty((String) stringObjectMap.get("agencyProjectLeaderNo"))?"":(String) stringObjectMap.get("agencyProjectLeaderNo");

                String  sql = "INSERT INTO e_form VALUES('"+businessCategoryId+"','"+formId+"','"+businessCategory+
                        "','" +deptName+"','"+deptId+"','"+projectName+"','"+projectId+"',"+currentTime+",'"+projectLeader+"','"+
                        projectLeaderId+"','"+businessId+"','"+businessName+"','"+projectManager+"','"
                        +projectManagerNo+"','"+reportName+"','"+projectLeaderNo+"','"+projectCode+"','"+members+"','"
                        +referReportName+"','"+reportNo+"','"+reportId+"','"+projectGlobalId+"','"+referReportId+"','"+reportType+
                        "','"+businessNo+"','"+agencyId+"','"+agencyNo+"','"+sectionName+"','"+agencyName+
                        "','"+costName+"','"+costNo+"','"+costId+"','"+applicationNo+"','"+otherNo+"','"+reportTypeCounts+
                        "','"+newProjectLeader+"','"+newProjectName+"','"+newProjectCode+"','"+businessStatusRemark+
                        "',"+businessStartTime+","+businessEndTime+",'"+businessStatus+"','"+newProjectLeaderNo+
                        "','"+projectCompany+"','"+agencyProjectLeaderId+"','"+agencyProjectLeader+"','"+agencyProjectLeaderNo+"')";
                int res= SqlUtil.doNoQuery(sql);
                String bizObjectSchemaCode=(String) stringObjectMap.get("bizObjectSchemaCode");
                logger.info("存储表单数据————bizObjectSchemaCode——"+bizObjectSchemaCode);
                if("projectElectronicArchive".equals(bizObjectSchemaCode)){
                    List<Map<String, Object>> attachments = (List<Map<String, Object>>) stringObjectMap.get("Attachments");
                    logger.info("存储表单数据————attachments——"+attachments);
                    createAttachments(attachments,formId);
                }else if("businessRemoval".equals(bizObjectSchemaCode)){
                    List<Map<String, Object>> bmsReports = (List<Map<String, Object>>) stringObjectMap.get("bmsReport");
                    logger.info("存储表单数据————bmsReport——"+bmsReports);
                    createBmsReports(bmsReports,formId);
                }
                if(res>0){
                    result.put("formId", formId);
                    result.put("state", "200");
                    result.put("date", AESUtil.encrypt(String.valueOf(currentTime)));
                    result.put("msg", "success");
                    return result;
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     *  创建报告号
     * @param bmsReports
     * @param formId
     */
    private void createBmsReports(List<Map<String, Object>> bmsReports, String formId) {
        if(bmsReports==null||bmsReports.isEmpty()){
        }else {
            for (Map<String, Object> map:bmsReports) {
                String objectId= UUID.randomUUID().toString();
                String reportNo = (String) map.get("reportNo");
                String reportName = (String) map.get("reportName");
                String sql="INSERT INTO e_report (formId, reportNo, reportName, time, objectId) VALUES('"+formId+"','"+reportNo+"','"+
                        reportName+"',now(),'"+objectId+"')";
                int res= SqlUtil.doNoQuery(sql);
            }
        }
    }

    private void createAttachments(List<Map<String, Object>> attachments,String formId) {
        if(attachments==null||attachments.isEmpty()){
        }else {
            for (Map<String, Object> map:attachments) {
                String objectId= UUID.randomUUID().toString();
                String name = (String) map.get("attachmentName");
                String link = (String) map.get("link");
                String whetherMust = (String) map.get("whetherMust");
                String instructions = (String) map.get("instructions");
                String fileName = (String) map.get("fileName");
                logger.info("存储表单数据————name——"+name);
                String sql="INSERT INTO e_attachment VALUES('"+formId+"','"+name+"','"+
                        link+"',now(),'"+whetherMust+"','"+instructions+"','"+
                        fileName+"','"+objectId+"',0)";
                int res= SqlUtil.doNoQuery(sql);
            }
        }

    }

    @RequestMapping("/validity")
    public JSONObject validity(String date){
        JSONObject result = new JSONObject();
        try {
            logger.info("获取连接的有效期————date——"+date);
            if(StringUtils.isNotBlank(date)){
                    date=AESUtil.decrypt(date);
                    //当前时间
                    long currentTime=System.currentTimeMillis();
                    //返回的时间
                    result.put("data", true);
                    long numStar= Long.valueOf(date).longValue();
                    if(currentTime-numStar>1000*10){
                        result.put("data", false);
                    }
                    result.put("state", "200");

                    result.put("msg", "success");
                    return result;


            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @RequestMapping("/beginInstance")
    public JSONObject beginInstance(String formId){
        JSONObject result = new JSONObject();
        try {
            logger.info("存储表单数据————formId——"+formId);
            if(StringUtils.isNotBlank(formId)){
                String sql="select * from e_form  where formId = '" +formId+"'";
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    String businessCategory= entityRows.get(0).getString("businessCategory");
                    String businessCategoryId= entityRows.get(0).getString("businessCategoryId");
                    String deptName= entityRows.get(0).getString("deptName");
                    String deptId= entityRows.get(0).getString("deptId");
                    String projectName= entityRows.get(0).getString("projectName");
                    String projectId= entityRows.get(0).getString("projectId");
                    String projectLeader= entityRows.get(0).getString("projectLeader");
                    String projectLeaderId= entityRows.get(0).getString("projectLeaderId");
                    String businessId=entityRows.get(0).getString("businessId");
                    String businessName=entityRows.get(0).getString("businessName");
                    String projectManager=entityRows.get(0).getString("projectManager");
                    String projectManagerNo=entityRows.get(0).getString("projectManagerNo");
                    String reportName=entityRows.get(0).getString("reportName");
                    String projectLeaderNo=entityRows.get(0).getString("projectLeaderNo");
                    String projectCode=entityRows.get(0).getString("projectCode");
                    String members=entityRows.get(0).getString("members");
                    String referReportName=entityRows.get(0).getString("referReportName");
                    String reportNo=entityRows.get(0).getString("reportNo");
                    String reportId=entityRows.get(0).getString("reportId");
                    String projectGlobalId=entityRows.get(0).getString("projectGlobalId");
                    String referReportId=entityRows.get(0).getString("referReportId");
                    String reportType=entityRows.get(0).getString("reportType");
                    String businessNo=entityRows.get(0).getString("businessNo");
                    String agencyId=entityRows.get(0).getString("agencyId");
                    String agencyNo=entityRows.get(0).getString("agencyNo");
                    String sectionName=entityRows.get(0).getString("sectionName");
                    String agencyName=entityRows.get(0).getString("agencyName");
                    String costName=entityRows.get(0).getString("costName");
                    String costNo=entityRows.get(0).getString("costNo");
                    String costId=entityRows.get(0).getString("costId");
                    //沪报建编号
                    String applicationNo=entityRows.get(0).getString("applicationNo");
                    //沪标段号
                    String otherNo=entityRows.get(0).getString("otherNo");
                    String reportTypeCounts=entityRows.get(0).getString("reportTypeCounts");
                    String newProjectLeader=entityRows.get(0).getString("newProjectLeader");
                    String newProjectName=entityRows.get(0).getString("newProjectName");
                    String newProjectCode=entityRows.get(0).getString("newProjectCode");
                    String businessStatusRemark=entityRows.get(0).getString("businessStatusRemark");
                    Date businessStartTime=entityRows.get(0).getDate("businessStartTime");
                    Date businessEndTime=entityRows.get(0).getDate("businessEndTime");
                    String businessStatus=entityRows.get(0).getString("businessStatus");
                    String newProjectLeaderNo=entityRows.get(0).getString("newProjectLeaderNo");
                    String projectCompany=entityRows.get(0).getString("projectCompany");

                    String agencyProjectLeaderId=entityRows.get(0).getString("agencyProjectLeaderId");
                    String agencyProjectLeader=entityRows.get(0).getString("agencyProjectLeader");
                    String agencyProjectLeaderNo=entityRows.get(0).getString("agencyProjectLeaderNo");
                    HashMap<String, Object> map = new HashMap<>();
                    map.put("businessCategory",businessCategory);
                    map.put("businessCategoryId",businessCategoryId);
                    map.put("deptName",deptName);
                    map.put("deptId",deptId);
                    map.put("projectName",projectName);
                    map.put("projectId",projectId);
                    map.put("projectLeader",projectLeader);
                    map.put("projectLeaderId",projectLeaderId);
                    map.put("businessId",businessId);
                    map.put("businessName",businessName);
                    map.put("projectManager",projectManager);
                    map.put("projectManagerNo",projectManagerNo);
                    map.put("reportName",reportName);
                    map.put("projectLeaderNo",projectLeaderNo);
                    map.put("projectCode",projectCode);
                    map.put("referReportName",referReportName);
                    map.put("members",members);
                    map.put("reportNo",reportNo);
                    map.put("reportId",reportId);
                    map.put("projectGlobalId",projectGlobalId);
                    map.put("referReportId",referReportId);
                    map.put("reportType",reportType);
                    map.put("businessNo",businessNo);
                    map.put("agencyId",agencyId);
                    map.put("agencyNo",agencyNo);
                    map.put("sectionName",sectionName);
                    map.put("agencyName",agencyName);
                    map.put("costName",costName);
                    map.put("costNo",costNo);
                    map.put("costId",costId);
                    //沪报建编号
                    map.put("applicationNo",applicationNo);
                    //沪标段号
                    map.put("otherNo",otherNo);
                    map.put("reportTypeCounts",reportTypeCounts);
                    map.put("newProjectLeader",newProjectLeader);
                    map.put("newProjectName",newProjectName);
                    map.put("newProjectCode",newProjectCode);
                    map.put("businessStatusRemark",businessStatusRemark);
                    map.put("businessStartTime",businessStartTime);
                    map.put("businessEndTime",businessEndTime);
                    map.put("businessStatus",businessStatus);
                    map.put("newProjectLeaderNo",newProjectLeaderNo);
                    map.put("projectCompany",projectCompany);

                    map.put("agencyProjectLeaderId",agencyProjectLeaderId);
                    map.put("agencyProjectLeader",agencyProjectLeader);
                    map.put("agencyProjectLeaderNo",agencyProjectLeaderNo);

                    result.put("state", "200");
                    result.put("data", map);
                    result.put("msg", "success");
                    return result;
                }

            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @RequestMapping("/beginAttem")
    public JSONObject beginAttem(String formId){
        JSONObject result = new JSONObject();
        try {
            logger.info("存储表单数据————formId——"+formId);
            if(StringUtils.isNotBlank(formId)){
                String sql="select * from  e_attachment where formId = '" +formId+"' ORDER BY id desc";
                ArrayList<HashMap<String, Object>> list = new ArrayList<>();
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    for (DataRow dataRow:entityRows) {
                        String AttachmentName= dataRow.getString("AttachmentName");
                        String link= dataRow.getString("link");
                        String whetherMust= dataRow.getString("whetherMust");
                        String instructions= dataRow.getString("instructions");
                        String fileName= dataRow.getString("fileName");
                        HashMap<String, Object> map = new HashMap<>();
                        map.put("AttachmentName",AttachmentName);
                        map.put("whetherMust",whetherMust);
                        map.put("link",link);
                        map.put("instructions",instructions);
                        map.put("fileName",fileName);
                        list.add(map);
                    }
                }
                result.put("state", "200");
                result.put("data", list);
                result.put("msg", "success");
                return result;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @RequestMapping("/beginReport")
    public JSONObject beginReport(String formId){
        JSONObject result = new JSONObject();
        try {
            logger.info("存储表单数据————formId——"+formId);
            if(StringUtils.isNotBlank(formId)){
                String sql="select * from e_report where formId = '" +formId+"' ORDER BY time desc";
                ArrayList<HashMap<String, Object>> list = new ArrayList<>();
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    for (DataRow dataRow:entityRows) {
                        String reportName= dataRow.getString("reportName");
                        String reportNo= dataRow.getString("reportNo");
                        HashMap<String, Object> map = new HashMap<>();
                        map.put("reportName",reportName);
                        map.put("reportNo",reportNo);
                        list.add(map);
                    }
                }
                result.put("state", "200");
                result.put("data", list);
                result.put("msg", "success");
                return result;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    //保存后删除该数据
    @RequestMapping("/delForm")
    public JSONObject delForm(String formId){
        JSONObject result = new JSONObject();
        try {
            logger.info("删除存储表单数据————formId——"+formId);
            if(StringUtils.isNotBlank(formId)){
                String sql="delete from e_form  where formId = '" +formId+"'";
                int res= SqlUtil.doNoQuery(sql);
                if(res>0){
                    logger.info("删除存储表单数据————成功");
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
    @RequestMapping("/validityTime")
    public JSONObject validity(){
        JSONObject result = new JSONObject();
        //当前时间
        long currentTime=System.currentTimeMillis();
        result.put("state", "200");
        result.put("data", AESUtil.encrypt(String.valueOf(currentTime)));
        result.put("msg", "success");
        return result;
    }
    //业务系统代办
    @RequestMapping("/unfinish")
    public JSONObject unfinish(String code){
        JSONObject result = new JSONObject();
        logger.info("获取代办————code---"+code);
        if(StringUtils.isNotBlank(code)){
            try {
                String sql="SELECT count(1) as num FROM OT_WORKITEM w LEFT JOIN OT_USER u on u.OBJECTID=w.Participant or u.OBJECTID=w.Delegant or u.OBJECTID=w.Forwarder WHERE u.CODE='" +code+"'";
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    String num= entityRows.get(0).getString("num");
                    result.put("state", "200");
                    result.put("data", num);
                    result.put("msg", "success");
                    return result;
                }

            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("msg", "success");
        return result;
    }
    //业务系统代办
    @RequestMapping("/circulate")
    public JSONObject circulate(String code){
        JSONObject result = new JSONObject();
        logger.info("获取待閱————code---"+code);
        if(StringUtils.isNotBlank(code)){
            try {
                String sql="SELECT COUNT(1) as nums FROM\n" +
                        "(\n" +
                        "SELECT\n" +
                        "\t(select REPLACE(System,\";\",\"\") from i_processsystem where IsDisable='否' and ProcessCode=item.WorkflowCode limit 1) AS systemName,\n" +
                        "\titem.Participant,\n" +
                        "\titem.ObjectID,\n" +
                        "\tu.CODE\n" +
                        "FROM\n" +
                        "\tot_circulateitem item \n" +
                        "LEFT JOIN OT_USER u on u.OBJECTID=item.Participant \n" +
                        "UNION \n" +
                        "SELECT\n" +
                        "\t(select REPLACE(System,\";\",\"\") from i_processsystem where IsDisable='否' and ProcessCode=item.WorkflowCode limit 1) AS systemName,\n" +
                        "\titem.Participant,\n" +
                        "\titem.ObjectID,\n" +
                        "\tu.CODE\n" +
                        "FROM\n" +
                        "\tot_circulateitem item \n" +
                        "LEFT JOIN OT_USER u on u.OBJECTID=item.Delegant \n" +
                        ") A WHERE systemName = 'EC' AND CODE = '" +code+"'";
                logger.info("待阅sql = {}",sql);
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    String num= entityRows.get(0).getString("nums");
                    result.put("state", "200");
                    result.put("data", num);
                    result.put("msg", "success");
                    return result;
                }

            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("msg", "success");
        return result;
    }
    //查看登录人是不是本人的待处理的流程
    @RequestMapping("/workItem")
    public JSONObject workItem(String code, String InstanceId){
        JSONObject result = new JSONObject();
        logger.info("获取代办————InstanceId--code-"+InstanceId+"----"+code);
        if(StringUtils.isNotBlank(code)){
            try {
                String data = "";
                String WorkItemID = "";
                String sql="SELECT w.ObjectID FROM OT_WORKITEM w LEFT JOIN OT_USER u on u.OBJECTID=w.Participant WHERE w.InstanceId='" +InstanceId+"' and u.code='" +code+"'";
                String dySql="SELECT w.ObjectID FROM OT_CirculateItem   w LEFT JOIN OT_USER u on u.OBJECTID=w.Participant WHERE w.InstanceId='" +InstanceId+"' and u.code='" +code+"'";
                String finishedSql="SELECT w.ObjectID,u.code FROM ot_workitemfinished w LEFT JOIN OT_USER u on u.OBJECTID=w.Participant WHERE w.InstanceId='" +InstanceId+"' and u.code='" +code+"' ORDER BY w.FinishTime,w.TokenId desc";
                String yySql="SELECT w.ObjectID,u.code FROM OT_CirculateItemFinished   w LEFT JOIN OT_USER u on u.OBJECTID=w.Participant WHERE w.InstanceId='" +InstanceId+"' and u.code='" +code+"' ORDER BY w.FinishTime,w.TokenId desc";
                //待办
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty() && entityRows.size()>0) {
                    data = "0";
                    WorkItemID = entityRows.get(0).getString("ObjectID");
                }
                //待阅
                if("".equals(data)){
                    table = SqlUtil.doQuery(dySql);
                    entityRows = table.getEntityRows();
                    if(!entityRows.isEmpty() && entityRows.size()>0) {
                        data = "0";
                        WorkItemID = entityRows.get(0).getString("ObjectID");
                    }
                }
                //已办
                if("".equals(data)){
                    DataTable finishedTable = SqlUtil.doQuery(finishedSql);
                    List<DataRow> finishedEntityRows = finishedTable.getEntityRows();
                    if(!finishedEntityRows.isEmpty()&&finishedEntityRows.size()>0) {
                        data = "1";
                        WorkItemID = finishedEntityRows.get(0).getString("ObjectID");
                    }
                }
                //已阅
                if("".equals(data)){
                    DataTable finishedTable = SqlUtil.doQuery(yySql);
                    List<DataRow> finishedEntityRows = finishedTable.getEntityRows();
                    if(!finishedEntityRows.isEmpty()&&finishedEntityRows.size()>0) {
                        data = "1";
                        WorkItemID = finishedEntityRows.get(0).getString("ObjectID");
                    }
                }

                if(!data.equals("1")&&!data.equals("0")){
                    //查看
                    data = "2";
                }
                result.put("data", data);
                result.put("WorkItemID", WorkItemID);
                result.put("state", "200");
                result.put("msg", "success");
                return result;
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("msg", "success");
        return result;
    }
    //代办任务id
    @RequestMapping("/workItemId")
    public JSONObject WorkItemID(String code,String instanceId){
        JSONObject result = new JSONObject();
        logger.info("获取代办id————instanceId--code-"+instanceId+"----"+code);
        if(StringUtils.isNotBlank(code)){
            try {
                String sql="SELECT o.bizObjectSchemaCode,w.OBJECTID from ot_instancecontext o " +
                        "LEFT JOIN ot_workitem w\n" +
                        "\tON w.InstanceId=o.OBJECTID " +
                        "LEFT JOIN ot_user u ON u.OBJECTID=w.Participant\n" +
                        "\tWHERE o.OBJECTID='"+instanceId+"' AND u.`Code`='"+code+"'";

                String finishedSql="SELECT o.bizObjectSchemaCode,w.OBJECTID from ot_instancecontext o " +
                        "LEFT JOIN ot_workitemfinished w\n" +
                        "\tON w.InstanceId=o.OBJECTID " +
                        "LEFT JOIN ot_user u ON u.OBJECTID=w.Participant\n" +
                        "\tWHERE o.OBJECTID='"+instanceId+"' AND u.`Code`='"+code+"' ORDER BY w.FinishTime,w.TokenId desc";
                //代办
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    String objectId= entityRows.get(0).getString("OBJECTID");
                    String bizObjectSchemaCode= entityRows.get(0).getString("bizObjectSchemaCode");
                    logger.info("获取代办id————objectId---"+objectId);
                    result.put("objectId", objectId);
                    result.put("bizObjectSchemaCode",bizObjectSchemaCode);
                    result.put("state", "200");
                    result.put("msg", "success");
                    return result;
                }
                //已办
                DataTable finishedTable = SqlUtil.doQuery(finishedSql);
                List<DataRow> finishedEntityRows = finishedTable.getEntityRows();
                if(!finishedEntityRows.isEmpty()&&finishedEntityRows.size()>0) {
                    String objectId= finishedEntityRows.get(0).getString("OBJECTID");
                    String bizObjectSchemaCode= finishedEntityRows.get(0).getString("bizObjectSchemaCode");
                    logger.info("获取已办id————objectId---"+objectId);
                    result.put("objectId", objectId);
                    result.put("bizObjectSchemaCode",bizObjectSchemaCode);
                    result.put("state", "200");
                    result.put("msg", "success");
                    return result;
                }

            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("msg", "success");
        return result;
    }
    //获取所部级审批人
    @RequestMapping("/magnitude")
    public JSONObject magnitude(String deptId,String projectLeaderApprove,String projectLeaderNo){
        JSONObject result = new JSONObject();
        logger.info("获取部级审批人————deptId---"+deptId);
        if(StringUtils.isNotBlank(deptId)){
            try {
                Map<String, Object> magnitude = magnitudeService.getMagnitude(deptId);


                HashMap<String, Object> map = new HashMap<>();
                    map.put("departmentManager", magnitude.get("departmentManager"));
                    map.put("technicalDirector", magnitude.get("technicalDirector"));
                    map.put("shortName", magnitude.get("shortName"));
                    map.put("labelName", magnitude.get("labelName"));

                    if(projectLeaderNo!=null && !"".equals(projectLeaderNo)) {
                    	if(projectLeaderNo.equals(projectLeaderApprove)) {
                    		Map<String, Object> leaderNoMap = magnitudeService.getProjectLeader(projectLeaderNo);
                    		if(leaderNoMap.get("job_no")!=null && magnitude.get("departmentManager")!=null) {
                    			String jobNo = leaderNoMap.get("job_no").toString();
                    			String departmentManager = leaderNoMap.get("departmentManager").toString();
                    			if(!"".equals(jobNo) && jobNo.equals(departmentManager)) {
                    				 map.put("departmentManager", null);
                    			}
                    		}

                    		if(leaderNoMap.get("job_no")!=null && magnitude.get("technicalDirector")!=null) {
                    			String jobNo = leaderNoMap.get("job_no").toString();
                    			String technicalDirector = leaderNoMap.get("technicalDirector").toString();
                    			if(!"".equals(jobNo) && jobNo.equals(technicalDirector)) {
                    				 map.put("technicalDirector", null);
                    			}
                    		}
                    	}
                    }

                    result.put("state", "200");
                    result.put("data", map);
                    result.put("msg", "success");
                    return result;
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("msg", "success");
        return result;
    }
    //获取印章管理员
    @RequestMapping("/SealCustodyUser")
    public JSONObject SealCustodyUser(String objectId,String workflowCode){
        ArrayList<String> list = new ArrayList<>();
        JSONObject result = new JSONObject();
        logger.info("获取印章————code---"+objectId);
        if(StringUtils.isNotBlank(objectId)){
            try {
                String sql="SELECT useSeal FROM i_"+workflowCode+" WHERE objectId='" +objectId+"'";
                DataTable table = SqlUtil.doQuery(sql);
                List<DataRow> entityRows = table.getEntityRows();
                if(!entityRows.isEmpty()&&entityRows.size()>0) {
                    String useSeal= entityRows.get(0).getString("useSeal");
                    if(StringUtils.isNotBlank(useSeal)){
                        String[] stringss = useSeal.split(";");
                        for (String seal:stringss) {
                            //list.add(seal);
                            String sql2="SELECT sealCustodyUser FROM i_officialseal WHERE sealName='" +seal+"'";
                            DataTable table2 = SqlUtil.doQuery(sql2);
                            List<DataRow> entityRows2 = table2.getEntityRows();
                            if(!entityRows2.isEmpty()&&entityRows2.size()>0) {
                                String sealCustodyUser= entityRows2.get(0).getString("sealCustodyUser");
                                if(StringUtils.isNotBlank(sealCustodyUser)){
                                    ArrayList<String> arrayList = GetSeal(sealCustodyUser);
                                    for (String seal2:arrayList) {
                                        list.add(seal2);
                                    }
                                }
                            }
                        }
                    }
                }
              if(!list.isEmpty()&&list.size()>0){
                  HashSet h = new HashSet(list);
                  list.clear();
                  list.addAll(h);
              }

            }catch (Exception e){
                e.printStackTrace();
            }
        }
        result.put("data", list);
        result.put("msg", "success");
        return result;
    }
    public static ArrayList<String> GetSeal(String args) {

        String[] strings = args.split("><");
        ArrayList<String> list = new ArrayList<>();
        int num=strings.length;
        if(num>=3){
            for (int i = 1; i <num ; i++) {
                String now=strings[i];
                String object=now.substring(7,now.length()-8);
                list.add(object);
            }
        }
        return list;
    }
    public static ArrayList<String> GetSealUser(ArrayList<String> list) {
        if(!list.isEmpty()&&list.size()>0){
            for (String s:list) {

            }
        }
    return null;

    }

}
